import xlrd
import xlwt
from datetime import datetime
import os
import os, shutil
from xml.etree.ElementTree import ElementTree, Element

LANGUAGE_DATABASE = '英语母语校-对20181012.xlsx'

SAVE_FILE = '正在使用.xls'
SHEET_NAME = u'Sheet1'

FILE_TYPE = ['strings.xml', 'strings_bt.xml', 'home_strings.xml', 'arrays.xml', 'string_arry.xml', 'string_arry.xml', 'countries.xml', 'timezones.xml']
CHINESE_VALUE = 'values-zh-rCN'
LANGUAGE_TYPES = ['values-de-rDE', 'values-en-rUS', 'values-es-rES', 'values-fr-rFR', 'values-it-rIT', 'values-ar-rAE',
                  'values-pt-rPT', 'values-ru-rRU', 'values-th-rTH', 'values-zh-rCN', 'values-ko-rKR', 'values-zh-rHK']

NEED_NODES = ['string', 'item', 'country', 'timezone']


def find_values():
    try:
        workbook = xlwt.Workbook()
        sheet3 = workbook.add_sheet(SHEET_NAME, cell_overwrite_ok=True)
        style = xlwt.XFStyle()

        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = 5
        # 打开当前路径下的excel表格
        file_handler = xlrd.open_workbook(LANGUAGE_DATABASE)
        page = file_handler.sheet_by_name(SHEET_NAME)

        # 获取第一行的数据，即语言种类
        language_types = page.row_values(0)
        # 找到简体中文所在列
        col_chinese = language_types.index(CHINESE_VALUE)
        # 找到所有的简体中文数据，即简体中文所在列
        exist_data = page.col_values(col_chinese)

        # 遍历当前路径
        work_dir = os.getcwd()
        for parent, dir_names, file_names in os.walk(work_dir):
            for file_name in file_names:
                # 过滤指定的文件
                if file_name in FILE_TYPE:
                    # 完整文件路径
                    file_path = os.path.join(parent, file_name)
                    # 遍历指定语言
                    for language in LANGUAGE_TYPES:
                        if language in language_types:
                            # 获取该语言所在列
                            col_language = language_types.index(language)
                            # 根据资源文件生成XML tree
                            tree = ElementTree()
                            tree.parse(file_path)
                            root = tree.getroot()
                            # 遍历指定的节点
                            for need_node in NEED_NODES:
                                # 找到该节点在XML中对应的位置，并修改Value
                                for name in root.iter(need_node):
                                    # 找到已翻译的中文字符串所在行
                                    row = 0
                                    # 特殊处理 转小写后再移除前后空格
                                    for tmp_exist_data in exist_data:
                                        if tmp_exist_data is not None and name.text is not None:
                                            if tmp_exist_data.lower().strip() == name.text.lower().strip():
                                                # 相等则修改节点内容
                                                name.text = page.cell(row, col_language).value
                                                sheet3.write(row, col_language, name.text, style)
                                                break
                                        row += 1
        workbook.save(SAVE_FILE)
    except Exception as e:
        print(e)


if __name__ == "__main__":
    # 当前文件的路径
    pwd = os.getcwd()
    # 当前文件的父路径
    father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
    LANGUAGE_DATABASE = os.path.join(father_path,LANGUAGE_DATABASE)
    find_values()
